﻿-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[CartItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [CartItem]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CartItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE CartItem
(
	CartItemID int IDENTITY(1,1) NOT NULL,
	CartOwnerID varchar(255) NOT NULL,
	CommodityID int NOT NULL,
	Count int NOT NULL,
	CreatedBy varchar(255),
	Created datetime NOT NULL,
	CONSTRAINT [CartItem$PK] PRIMARY KEY CLUSTERED 
	(
		CartItemID ASC
	)
)
END
GO

CREATE NONCLUSTERED INDEX CartItem_CartOwnerID ON dbo.CartItem
	(
	CartOwnerID
	)
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[ScheduleItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ScheduleItem]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ScheduleItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE ScheduleItem
(
	ScheduleItemID int IDENTITY(1,1) NOT NULL,
	CourseID int NOT NULL,
	EventDate datetime NOT NULL,
	DurationHours int NOT NULL,
	Note varchar(4000),
	CONSTRAINT [ScheduleItemID$PK] PRIMARY KEY CLUSTERED 
	(
		ScheduleItemID ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Course]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Course
(
	CourseID int IDENTITY(1,1) NOT NULL,
	CommodityID int NOT NULL,
	CategoryID int NOT NULL,
	DurationHours int not null default 0,
	DurationDays int not null default 0,
	CourseProgramm ntext,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [Course$PK] PRIMARY KEY NONCLUSTERED
	(
		[CourseID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Software]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Software]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Software]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Software
(
	SoftwareID int IDENTITY(1,1) NOT NULL,
	CommodityID int NOT NULL,
	CategoryID int NOT NULL,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [Software$PK] PRIMARY KEY NONCLUSTERED
	(
		[SoftwareID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Hardware]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Hardware]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Hardware]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Hardware
(
	HardwareID int IDENTITY(1,1) NOT NULL,
	CommodityID int NOT NULL,
	CategoryID int NOT NULL,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	InternalCode varchar(255) NOT NULL,
	CONSTRAINT [Hardware$PK] PRIMARY KEY NONCLUSTERED
	(
		[HardwareID] ASC
	)
)
END
GO

CREATE NONCLUSTERED INDEX [Hardware_InternalCodeUnique] ON [dbo].[Hardware] 
(
	[InternalCode] ASC
)
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Certification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Certification]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Certification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Certification
(
	CertificationID int IDENTITY(1,1) NOT NULL,
	CommodityID int NOT NULL,
	CategoryID int NOT NULL,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [Certification$PK] PRIMARY KEY NONCLUSTERED
	(
		[CertificationID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Commodity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Commodity
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Commodity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Commodity
(
	CommodityID int IDENTITY(1,1) NOT NULL,
	Code varchar(256) NOT NULL,
	Name varchar(4000),
	Price numeric(19, 2),
	SortingID int not null default 0,
	Descr ntext,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CommodityType int,
	Closed datetime,
	CONSTRAINT [Commodity$PK] PRIMARY KEY NONCLUSTERED
	(
		[CommodityID] ASC
	)
)
END
GO

CREATE UNIQUE NONCLUSTERED INDEX [Commodity_CodeUnique] ON [dbo].[Commodity] 
(
	[Code] ASC
)
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Category
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Category
(
	CategoryID int IDENTITY(1,1) NOT NULL,
	Name varchar(4000) NOT NULL,
	SortingID int not null default 0,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [Category$PK] PRIMARY KEY NONCLUSTERED
	(
		[CategoryID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[NewsList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table NewsList
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewsList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE NewsList
(
	NewsID int IDENTITY(1,1) NOT NULL,
	SortingID int not null default 0,
	Header varchar(4000),
	Body ntext,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [News$PK] PRIMARY KEY NONCLUSTERED
	(
		[NewsID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[PageContent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table PageContent
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PageContent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE PageContent
(
	PageContentID int IDENTITY(1,1) NOT NULL,
	PageContentUniqName varchar(900) NOT NULL,
	Header varchar(4000),
	Body ntext,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [PageContent$PK] PRIMARY KEY NONCLUSTERED
	(
		[PageContentID] ASC
	),
	CONSTRAINT [PageContent$UK1] UNIQUE NONCLUSTERED
	(
		[PageContentUniqName] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Feedback]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Feedback
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Feedback]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE Feedback
(
	FeedbackID int IDENTITY(1,1) NOT NULL,
	FeedbackOwnerID varchar(255) NOT NULL,
	Author varchar(255),
	Email varchar(255),
	Body ntext,
	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,
	CONSTRAINT [Feedback$PK] PRIMARY KEY NONCLUSTERED
	(
		[FeedbackID] ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Order]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Order]
(
	OrderID int IDENTITY(1,1) NOT NULL,
	OrderOwnerID varchar(255) NOT NULL,

	FirstName varchar(255),
	LastName varchar(255),
	[Address] varchar(4000),
	Country varchar(255),
	Phone varchar(255),
	Email varchar(255),

	CreatedBy varchar(255),
	Created datetime,
	UpdatedBy varchar(255),
	Updated datetime,

	[Notes] [ntext] NULL,
	CONSTRAINT [Order$PK] PRIMARY KEY NONCLUSTERED
	(
		OrderID ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[OrderItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table OrderItem
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrderItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE OrderItem
(
	OrderItemID int IDENTITY(1,1) NOT NULL,
	OrderID int NOT NULL,
	CommodityID int not null,
	Quantity int not null,
	UnitPrice numeric(19, 2) not null,
	CreatedBy varchar(255),
	Created datetime,
	CONSTRAINT [OrderItem$PK] PRIMARY KEY NONCLUSTERED
	(
		OrderItemID ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[SingleTag]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table SingleTag
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SingleTag]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE SingleTag
(
	Tag nvarchar(255) not null,
	SortingID int not null default 0,
	CONSTRAINT [SingleTag$PK] PRIMARY KEY NONCLUSTERED
	(
		Tag ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[TagsSet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table TagsSet
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TagsSet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE TagsSet
(
	CommodityID int not null,
	Tag nvarchar(255) not null,
	CONSTRAINT [TagsSet$PK] PRIMARY KEY NONCLUSTERED
	(
		CommodityID ASC, Tag ASC
	)
)
END
GO

-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--------------------------------------------------------------------------------
ALTER TABLE TagsSet WITH NOCHECK ADD CONSTRAINT [TagsSet$FK1] FOREIGN KEY(CommodityID)
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].TagsSet CHECK CONSTRAINT [TagsSet$FK1]
GO

--------------------------------------------------------------------------------
ALTER TABLE TagsSet WITH NOCHECK ADD CONSTRAINT [TagsSet$FK2] FOREIGN KEY(Tag)
REFERENCES [dbo].SingleTag (Tag)
GO

ALTER TABLE [dbo].TagsSet CHECK CONSTRAINT [TagsSet$FK2]
GO

--------------------------------------------------------------------------------
ALTER TABLE OrderItem WITH NOCHECK ADD CONSTRAINT [OrderItem$FK1] FOREIGN KEY(CommodityID)
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].OrderItem CHECK CONSTRAINT [OrderItem$FK1]
GO

--------------------------------------------------------------------------------
ALTER TABLE OrderItem WITH NOCHECK ADD CONSTRAINT [OrderItem$FK2] FOREIGN KEY(OrderID)
REFERENCES [dbo].[Order] ([OrderID])
GO

ALTER TABLE [dbo].OrderItem CHECK CONSTRAINT [OrderItem$FK2]
GO

--------------------------------------------------------------------------------
ALTER TABLE CartItem WITH NOCHECK ADD CONSTRAINT [CartItem$FK1] FOREIGN KEY(CommodityID)
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].CartItem CHECK CONSTRAINT [CartItem$FK1]
GO

--------------------------------------------------------------------------------
ALTER TABLE ScheduleItem WITH NOCHECK ADD CONSTRAINT [ScheduleItem$FK1] FOREIGN KEY(CourseID)
REFERENCES [dbo].[Course] ([CourseID])
GO

ALTER TABLE [dbo].ScheduleItem CHECK CONSTRAINT [ScheduleItem$FK1]
GO

--------------------------------------------------------------------------------
ALTER TABLE [dbo].[Course] WITH NOCHECK ADD CONSTRAINT [Course$FK1] FOREIGN KEY([CommodityID])
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [Course$FK1]
GO

ALTER TABLE [dbo].[Course] WITH NOCHECK ADD CONSTRAINT [Course$FK2] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [Course$FK2]
GO

--------------------------------------------------------------------------------
ALTER TABLE [dbo].[Software] WITH NOCHECK ADD CONSTRAINT [Software$FK1] FOREIGN KEY([CommodityID])
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].[Software] CHECK CONSTRAINT [Software$FK1]
GO

ALTER TABLE [dbo].[Software] WITH NOCHECK ADD CONSTRAINT [Software$FK2] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[Software] CHECK CONSTRAINT [Software$FK2]
GO

--------------------------------------------------------------------------------
ALTER TABLE [dbo].[Hardware] WITH NOCHECK ADD CONSTRAINT [Hardware$FK1] FOREIGN KEY([CommodityID])
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].[Hardware] CHECK CONSTRAINT [Hardware$FK1]
GO

ALTER TABLE [dbo].[Hardware] WITH NOCHECK ADD CONSTRAINT [Hardware$FK2] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[Hardware] CHECK CONSTRAINT [Hardware$FK2]
GO

--------------------------------------------------------------------------------
ALTER TABLE [dbo].[Certification] WITH NOCHECK ADD CONSTRAINT [Certification$FK1] FOREIGN KEY([CommodityID])
REFERENCES [dbo].[Commodity] ([CommodityID])
GO

ALTER TABLE [dbo].[Certification] CHECK CONSTRAINT [Certification$FK1]
GO

ALTER TABLE [dbo].[Certification] WITH NOCHECK ADD CONSTRAINT [Certification$FK2] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[Certification] CHECK CONSTRAINT [Certification$FK2]
GO

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------